﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.ComponentModel;

namespace Inventario_HardSoft.Controlador
{
    class Ctrl_ImportarProgramas
    {

        internal void getTablefromExcel(DataGridView dg, string filename)
        {
            Microsoft.Office.Interop.Excel.Application xlApp;
            Workbook xlWorkBook;
            Worksheet xlWorkSheet;
            var missing = Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(filename, false, true, missing, missing, missing, true, Excel.XlPlatform.xlWindows, '\t', false, false, 0, false, true, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            Excel.Range xlRange = xlWorkSheet.UsedRange;
            Array myValues = (Array)xlRange.Cells.Value2;
            int vertical = myValues.GetLength(0);
            int horizontal = myValues.GetLength(1);
            System.Data.DataTable dt = new System.Data.DataTable();

            dg.Rows.Clear();
            dg.Columns.Clear();

            //obtenemos la informacion de la cabecera
            for (int i = 1; i <= horizontal; i++)
            {
                dt.Columns.Add(i.ToString());

                DataGridViewTextBoxColumn dgcolumn = new DataGridViewTextBoxColumn();
                dgcolumn.Name = (string)myValues.GetValue(1, i) ?? "vacio";
                dgcolumn.HeaderText = (string)myValues.GetValue(1, i) ?? string.Empty;
                dgcolumn.ValueType = typeof(string);
                dgcolumn.SortMode = DataGridViewColumnSortMode.NotSortable;
                dgcolumn.ReadOnly = true;
                dg.Columns.Add(dgcolumn);
            }

            //obtenemos la informacion de la fila
            for (int x = 2; x <= vertical; x++)
            {
                DataGridViewRow dgrow = new DataGridViewRow();
                dgrow.CreateCells(dg);

                for (int y = 1; y <= horizontal; y++)
                {
                    dgrow.Cells[y - 1].Value = myValues.GetValue(x, y) ?? string.Empty;
                }

                dg.Rows.Add(dgrow);
            }

            xlWorkBook.Close(false, missing, missing);
            xlApp.Workbooks.Close();
            xlApp.Quit();
            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlApp);
            Marshal.ReleaseComObject(xlWorkSheet);
            Marshal.ReleaseComObject(xlWorkBook);
            xlWorkSheet = null;
            xlWorkBook = null;
            xlApp = null;
            GC.Collect();
        }        
    }
}
